import java.sql.*;
import java.text.MessageFormat;
import java.util.Scanner;

public class mysqlLink {
    public static void main(String[] args) {
        //在外面定义这样好关闭资源
        // 这种使用sql的方法是调用一条指令开一次链接窗口，使用完了就右给关了，效率很低
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //加载jdbc的驱动包，只能在添加了驱动包的项目下使用
            Class.forName("com.mysql.cj.jdbc.Driver");
            //和数据库取得链接 获取的是物理链接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/study_db?characterEncoding=utf8" +
                            "&allowPublicKeyRetrieval=true&useSSL=false",
                    "root", "tang1");
            //创建statement对象传递sql指令
            statement = connection.createStatement();
            //执行sql指令
            System.out.println("输入名字:");
            Scanner in = new Scanner(System.in);
            String name = in.nextLine();
            //这样添加指令会出现sql注入的情况 会导致数据的泄露
            //' or 1 = 1;--

            String sql = "select 学号,name,chinese,math,english from students_mark where name = '" + name + "'";
//            String sql = "select 学号,name,chinese,math,english from students_mark";
            //接受sql指令返回的结果
            resultSet = statement.executeQuery(sql);
            //对结果集遍历，如果有就返回true
            while (resultSet.next()) {
                long id = resultSet.getLong(1);
                String StuName = resultSet.getString(2);
                double markC = resultSet.getDouble(3);
                double markM = resultSet.getDouble(4);
                double markE = resultSet.getDouble(5);
                System.out.println(MessageFormat.format("学生id = {0}  学生姓名 = {1}  语文成绩 = {2} 数学成绩 = {3} 英语成绩 = {4}"
                        , id, StuName, markC, markM, markE));
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源 后用先关
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}
